# ========= 操作MySQL数据库 ======

import pymysql

HOST = "localhost"
USER = 'root'
PWD = '123456'
DB = 'db01'


def create_table():
    """创建一个表"""
    # 1. 创建一个数据库连接
    # 3.8版本的python函数在使用时需要指定参数名，否则会使用默认的参数模板
    # mysql_conn = pymysql.connect(HOST, USER, PWD, DB)
    mysql_conn = db = pymysql.connect(host="localhost", user="root", password="123456", database="db01")  # 四个参数依次为：主机、用户名、密码、数据库
    # 2. 获取操作指针
    cursor = mysql_conn.cursor()
    # 3. 准备SQL语句
    sql = """Create Table student22
    (
     sno VARCHAR(100),  
     sname varchar(100), 
     gender varchar(100),  
     birthday date,      
     mobile varchar(100),  
     email VARCHAR(100),  
     address varchar(100), 
     createtime datetime DEFAULT now(), 
     CONSTRAINT Pk_sno Primary key (sno), 
     CONSTRAINT UQ_mobile UNIQUE(mobile), 
     CONSTRAINT UQ_email UNIQUE(email)  
    )"""
    # 执行（异常处理）
    try:
        # 执行
        cursor.execute(sql)
        # 创建成功
        print("表格创建成功！")
    except Exception as e:
        print("创建表出现异常，具体原因：" + str(e))


def insert_one_student(s_dict: dict, t_name: str):
    """
    把一条记录插入到表格
    :param stu_dict: 具体的记录
    :param t_name: 表格的名称
    :return: 是否成功
    """
    # 1. 创建一个数据库连接
    mysql_conn = db = pymysql.connect(host="localhost", user="root", password="123456", database="db01")  # 四个参数依次为：主机、用户名、密码、数据库

    # 2. 获取操作指针
    cursor = mysql_conn.cursor()
    # 3. 准备SQL语句
    sql = "Insert into " + t_name + " (sno, sname, gender, birthday, mobile, email, address) values " + \
          "('%s','%s','%s','%s','%s','%s','%s')" % (
          s_dict['sno'], s_dict['sname'], s_dict['gender'], s_dict['birthday'],
          s_dict['mobile'], s_dict['email'], s_dict['address'])
    print(sql)
    # 执行（异常处理）
    try:
        # 执行
        cursor.execute(sql)
        # 将修改立马提交到数据库
        mysql_conn.commit()
        # 打印：
        print("数据添加成功，影响的行数：" + str(cursor.rowcount))
    except Exception as e:
        # 取消对数据库的修改
        mysql_conn.rollback()
        # 打印具体错误信息
        print("创建表出现异常，具体原因：" + str(e))
    finally:
        # 关闭连接
        mysql_conn.close()


def select_students(sql: str, infos: list):
    """
    查询相应的学生信息
    :param sql: 提供的SQL语句
    :return: 返回结果
    """
    # 1. 创建一个数据库连接
    mysql_conn = db = pymysql.connect(host="localhost", user="root", password="123456", database="db01")  # 四个参数依次为：主机、用户名、密码、数据库
    # 2. 获取操作指针
    cursor = mysql_conn.cursor()
    # 执行（异常处理）
    try:
        # 执行
        cursor.execute(sql)
        # 获取数据库返回的结果fetchall、fetchone
        results = cursor.fetchall()
        # 定义一个集合
        students = []
        # 遍历
        for one in results:
            # 定义一个临时的字典
            temp_dict = {}.fromkeys(infos)
            # 遍历
            for index, value in enumerate(one):
                # 添加
                temp_dict[infos[index]] = value
            # 附加
            students.append(temp_dict)
        # 返回
        return students
    except Exception as e:
        print("获取数据出现异常，具体原因：" + str(e))
    finally:
        # 关闭数据库连接
        mysql_conn.close()


if __name__ == '__main__':
    # 创建表
    # create_table()
    # # === 添加记录 ====
    # student = {'sno': '95001', 'sname':'张志东', 'gender':'男', 'birthday':'1990-10-18',
    #            'mobile': '13911223344', 'email': 'zhangzd@163.com',
    #            'address': "山炮村111号！"}
    # insert_one_student(student, 'student')

    # =====获取数据=======
    sql = """select sno, sname, gender, birthday, mobile, email,address from student;"""
    infos = ['sno', 'sname', 'gender', 'birthday', 'mobile', 'email', 'address']
    student_list = select_students(sql, infos)
    print(student_list)
